package us.kg.kotoriforest.base;

import org.junit.Test;

import java.sql.*;
import java.util.Scanner;

public class TestJDBC {
    @Test
    public void test1_1() throws SQLException {
        // 测试单行单列的查询

        // 注册驱动

        // 构造连接
        Connection connection = DriverManager.getConnection("jdbc:mysql:///JDBC", "root", "");

        // 创建 SQL 语句
        PreparedStatement preparedStatement = connection.prepareStatement("SELECT COUNT(*) AS CNT FROM t_emp");

        // 执行 SQL 语句并得到结果
        ResultSet resultSet = preparedStatement.executeQuery();

        // 因为我们非常明确最后返回的结果只有一行 所以我们可以不用 while 循环 而只用一次 if 判断
        if (resultSet.next()) {
            // 得到结果
            int cnt1 = resultSet.getInt(1);
//            int cnt2 = resultSet.getInt("COUNT(*)");
            int cnt3 = resultSet.getInt("CNT");
            System.out.println(cnt1 + " " + /* cnt2 + " " + */ cnt3);
        }

        // 关闭资源
        resultSet.close();
        preparedStatement.close();
        connection.close();
    }

    @Test
    public void test1_2() throws SQLException {
        // 测试单行单列的查询 使用 try-with-resource 来释放资源

        try (Connection connection = DriverManager.getConnection("jdbc:mysql:///JDBC", "root", "");
             PreparedStatement preparedStatement = connection.prepareStatement("SELECT COUNT(*) AS CNT FROM t_emp");
             ResultSet resultSet = preparedStatement.executeQuery()) {

            if (resultSet.next()) {
                int cnt1 = resultSet.getInt(1);
                int cnt3 = resultSet.getInt("CNT");
                System.out.println(cnt1 + " " + cnt3);
            }
        }
    }

    @Test
    public void test2() {
        // 单行多列

        try (Connection connection = DriverManager.getConnection("jdbc:mysql:///JDBC", "root", "");
             PreparedStatement preparedStatement = connection.prepareStatement("SELECT * FROM t_emp WHERE emp_id = ?");
             Scanner scanner = new Scanner(System.in)) {

            // 读取用户输入
            System.out.print("请输入员工id: ");
            int emp_id = scanner.nextInt();

            // 用用户输入替换占位符
            preparedStatement.setInt(1, emp_id);

            try (ResultSet resultSet = preparedStatement.executeQuery()) {
                // 因为最后的结果只有一行 所以不需要循环

                if (resultSet.next()) {
                    // 因为结果有多列 所以我们可以全部取出来
                    int empId = resultSet.getInt("emp_id");
                    String empName = resultSet.getString("emp_name");
                    double empSalary = resultSet.getDouble("emp_salary");
                    int empAge = resultSet.getInt("emp_age");

                    System.out.println(empId + '\t' + empName + '\t' + empSalary + '\t' + empAge);
                }
            }

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Test
    public void test3() {
        // 多行多列

        try (Connection connection = DriverManager.getConnection("jdbc:mysql:///JDBC", "root", "");
             PreparedStatement preparedStatement = connection.prepareStatement("SELECT * FROM t_emp WHERE emp_age > ?");
             Scanner scanner = new Scanner(System.in)) {

            preparedStatement.setInt(1, 18);

            try (ResultSet resultSet = preparedStatement.executeQuery()) {

                while (resultSet.next()) {
                    // 因为结果有多列 所以我们可以全部取出来
                    int empId = resultSet.getInt("emp_id");
                    String empName = resultSet.getString("emp_name");
                    double empSalary = resultSet.getDouble("emp_salary");
                    int empAge = resultSet.getInt("emp_age");

                    System.out.println(empId + "\t" + empName + "\t" + empSalary + "\t" + empAge);
                }
            }

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Test
    public void test4() {
        // 新增数据

        try (Connection connection = DriverManager.getConnection("jdbc:mysql:///JDBC", "root", "");
             PreparedStatement preparedStatement = connection.prepareStatement("INSERT INTO t_emp(emp_name, emp_salary, emp_age) VALUES(?, ?, ?)")) {

            // 设置插入的数据
            preparedStatement.setString(1, "Jack");
            preparedStatement.setDouble(2, 11451.4);
            preparedStatement.setInt(3, 19);

            // 执行 SQL 语句 得到结果
            // 增、删、改 所用的方法都是 executeUpdate()
            // 其的返回值是表中受影响的行数
            int rows = preparedStatement.executeUpdate();

            if (rows > 0) {
                System.out.println("update success");
            } else {
                System.out.println("update fail");
            }

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Test
    public void test5() {
        // 修改数据

        // 新增数据

        try (Connection connection = DriverManager.getConnection("jdbc:mysql:///JDBC", "root", "");
             PreparedStatement preparedStatement = connection.prepareStatement("UPDATE t_emp SET emp_age = ? WHERE emp_id = ?")) {

            // 设置更改的数据
            preparedStatement.setInt(1, 29);
            preparedStatement.setInt(2, 6);

            // 执行 SQL 语句 得到结果
            // 增、删、改 所用的方法都是 executeUpdate()
            // 其的返回值是表中受影响的行数
            int rows = preparedStatement.executeUpdate();

            if (rows > 0) {
                System.out.println("update success");
            } else {
                System.out.println("update fail");
            }

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}
